﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using MyClassLibrary;
using MyDB;

namespace MyProgram
{
    public partial class FrmPatient : Form
    {
        public FrmPatient()
        {
            InitializeComponent();
        }
        private void FrmPatient_Load(object sender, EventArgs e)
        {
            FormBorderStyle = System.Windows.Forms.FormBorderStyle.None;
            this.dataGridView1.ScrollBars = ScrollBars.Vertical;
            toolStripComboBox1.SelectedIndex = 0;
            toolStripComboBox1.TextChanged += toolStripComboBox1_TextChanged;
            this.KeyPreview = true;  
            //
            //12346
        }


        void toolStripComboBox1_TextChanged(object sender, EventArgs e)
        {
            this.toolStripTextBox2.Focus();
            this.toolStripTextBox2.SelectAll();
        }

        private void btn_Add_Click(object sender, EventArgs e)  
        {
            FrmAdd g = new FrmAdd();
            g.ShowDialog();

           
            MedicareDataClassesDataContext db = new MedicareDataClassesDataContext();
            
            if (global::MyProgram.FrmAdd.u == 1) { }
            else
            {
                var y = from p in db.Patients
                        select new { p.病歷號碼, p.姓名, p.身分證字號, p.性別, p.出生年月日, p.連絡電話, p.地址, p.Email, p.VIP, 檢驗提醒 = p.檢驗是否提醒, 用藥提醒 = p.用藥是否提醒 };
                this.dataGridView1.DataSource = y.ToList();
            }

            toolStripStatusLabel1.Text = "總筆數 :" + dataGridView1.Rows.Count;
            
            if (dataGridView1.Rows.Count < 1)
            {
                toolStripStatusLabel2.Text = "0 / 0";
            }
            else
            {
                toolStripStatusLabel2.Text = dataGridView1.SelectedRows[0].Index + 1 + " / " + dataGridView1.Rows.Count;
            }

        }

        MedicareDataClassesDataContext db = new MedicareDataClassesDataContext();

        private void btn_Search_Click(object sender, EventArgs e)
        {
            if (toolStripTextBox2.Text == "")
            {
                var q = from p in db.Patients
                        select new { p.病歷號碼, p.姓名, p.身分證字號,p.性別,p.出生年月日, p.連絡電話, p.地址, p.Email, p.VIP, 檢驗提醒 = p.檢驗是否提醒, 用藥提醒 = p.用藥是否提醒 };                
                dataGridView1.DataSource = q.ToList();
            }
            else
            {
                switch (toolStripComboBox1.SelectedIndex)
                {
                    case 0:
                        var q = from p in db.Patients
                                where p.姓名.Contains(toolStripTextBox2.Text)
                                select new { p.病歷號碼, p.姓名, p.身分證字號,p.性別,p.出生年月日, p.連絡電話, p.地址, p.Email, p.VIP, 檢驗提醒 = p.檢驗是否提醒, 用藥提醒 = p.用藥是否提醒 };
                        dataGridView1.DataSource = q.ToList();
                        break;
                    case 1:
                        var w = from p in db.Patients
                                where p.身分證字號.Contains(toolStripTextBox2.Text)
                                select new { p.病歷號碼, p.姓名, p.身分證字號,p.性別, p.出生年月日, p.連絡電話, p.地址, p.Email, p.VIP, 檢驗提醒 = p.檢驗是否提醒, 用藥提醒 = p.用藥是否提醒 };
                        dataGridView1.DataSource = w.ToList();
                        break;
                    case 2:
                        DateTime x;
                        if (DateTime.TryParse(toolStripTextBox2.Text, out x))
                        {
                            var a = from p in db.Patients //要輸入齊全年月日
                                    where p.出生年月日 == DateTime.Parse(string.Format("{0:yyyy/M/d}", toolStripTextBox2.Text))
                                    select new { p.病歷號碼, p.姓名, p.身分證字號,p.性別, p.出生年月日, p.連絡電話, p.地址, p.Email, p.VIP, 檢驗提醒 = p.檢驗是否提醒, 用藥提醒 = p.用藥是否提醒 };
                            dataGridView1.DataSource = a.ToList();
                        }
                        break;
                    case 3:
                        var b = from p in db.Patients
                                where p.連絡電話.Contains(toolStripTextBox2.Text)
                                select new { p.病歷號碼, p.姓名, p.身分證字號,p.性別, p.出生年月日, p.連絡電話, p.地址, p.Email, p.VIP, 檢驗提醒 = p.檢驗是否提醒, 用藥提醒 = p.用藥是否提醒 };
                        dataGridView1.DataSource = b.ToList();
                        break;
                }
            }
            this.toolStripTextBox2.Focus();
            this.toolStripTextBox2.SelectAll();
            
            
                toolStripStatusLabel1.Text = "總筆數 :" + db.Patients.Count();
                if(dataGridView1.Rows.Count<1)
                {
                    toolStripStatusLabel2.Text ="0 / 0";
                }
            else
                {
                    toolStripStatusLabel2.Text = dataGridView1.SelectedRows[0].Index + 1 + " / " + dataGridView1.Rows.Count;
                }


        }

        private void btn_Edit_Click(object sender, EventArgs e)
        {
            if (dataGridView1.SelectedRows.Count==0 || dataGridView1.DataSource==null)
            {
                MessageBox.Show("請選擇欲查詢病患");
            }
            else
            {
                FrmEdit g = new FrmEdit();
                g.identity = dataGridView1.SelectedRows[0].Cells[2].Value.ToString();//告知何筆資料 帶入FrmEdit
                g.ShowDialog();

                if (g.pId != null)
                {
                    var q = from p in db.Patients
                            where p.身分證字號 == g.pId
                            select new { p.病歷號碼, p.姓名, p.身分證字號,p.性別, p.出生年月日, p.連絡電話, p.地址, p.Email, p.VIP, 檢驗提醒 = p.檢驗是否提醒, 用藥提醒 = p.用藥是否提醒 };
                    dataGridView1.DataSource = q.ToList();
                }
                else
                {
                    var q = from p in db.Patients
                            where p.身分證字號 == g.identity
                            select new { p.病歷號碼, p.姓名, p.身分證字號,p.性別, p.出生年月日, p.連絡電話, p.地址, p.Email, p.VIP, 檢驗提醒 = p.檢驗是否提醒, 用藥提醒 = p.用藥是否提醒 };
                    dataGridView1.DataSource = q.ToList();
                }

                    this.toolStripTextBox2.Focus();
                    this.toolStripTextBox2.SelectAll();
                    
                if (dataGridView1.Rows.Count < 1)
                    {
                        toolStripStatusLabel2.Text = "0 / 0";
                    }
                    else
                    {
                        toolStripStatusLabel2.Text = dataGridView1.SelectedRows[0].Index + 1 + " / " + dataGridView1.Rows.Count;
                    }
            }
        }

        public static int w;
        int t = 0;
        
        private void btn_Load_Click(object sender, EventArgs e)
        {
            MedicareDataClassesDataContext db = new MedicareDataClassesDataContext();
            w = (from p in db.Patients
                     select p.病患ID).Max();
            int h = 0;
            OpenFileDialog file = new OpenFileDialog();
            if (file.ShowDialog() == DialogResult.OK)
            {
                
                string fileName = file.FileName;
                string excelConn = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + fileName + "; Extended Properties = 'Excel 8.0;HDR=YES'"; //連線

                List<String> sheets = global::MyClassLibrary.Class1.getSheets(fileName);  //取得匯入資料的sheets名稱

                string targetSheet = sheets[0];  //固定只會入第一個sheet
                //string strExcelSelect = "SELECT * FROM [Sheet1$]"; 
                string strExcelSelect = String.Format("SELECT * FROM [{0}]", targetSheet);
                OleDbDataAdapter adapter = new OleDbDataAdapter(strExcelSelect, excelConn);

                DataTable fileData = new DataTable();
                adapter.Fill(fileData);
                dataGridView1.DataSource = fileData;
                //DB 
                using (SqlConnection Conn = new SqlConnection(Properties.Resources.MedicareConnectionString))
                {
                    string strDBInsert = "INSERT INTO Patients(姓名,身分證字號,性別,出生年月日,連絡電話,地址,Email,VIP,檢驗是否提醒,用藥是否提醒,病歷號碼) VALUES (@name,@id,@sex,@birth,@Phone,@addr,@email,@vip,@rexam,@rmed,@num)";
                    using (SqlCommand cmd = new SqlCommand(strDBInsert, Conn))
                    {
                        cmd.CommandType = CommandType.Text;
                        Conn.Open();
                     
                        for (int i = 0; i < dataGridView1.Rows.Count; i++)
                        {
                            
                            if (MyClassLibrary.Class1.checklemport(dataGridView1[1, i], ref h))//判斷身分證重複性
                            {
                                try
                                {
                                    cmd.Parameters.Clear();
                                    t++;
                                 
                                    cmd.Parameters.AddWithValue("@num",w+t );
                                    cmd.Parameters.AddWithValue("@name", Convert.ToString(dataGridView1[0, i].Value));
                                    cmd.Parameters.AddWithValue("@id", Convert.ToString(dataGridView1[1, i].Value));
                                    cmd.Parameters.AddWithValue("@sex", Convert.ToString(dataGridView1[2, i].Value));
                                    cmd.Parameters.AddWithValue("@birth", Convert.ToDateTime(dataGridView1[3, i].Value));
                                    cmd.Parameters.AddWithValue("@Phone", Convert.ToString(dataGridView1[4, i].Value));
                                    cmd.Parameters.AddWithValue("@addr", Convert.ToString(dataGridView1[5, i].Value));
                                    cmd.Parameters.AddWithValue("@email", Convert.ToString(dataGridView1[6, i].Value));
                                    cmd.Parameters.AddWithValue("@vip", Convert.ToBoolean(dataGridView1[7, i].Value));
                                    cmd.Parameters.AddWithValue("@rexam", Convert.ToBoolean(dataGridView1[8, i].Value));
                                    cmd.Parameters.AddWithValue("@rmed", Convert.ToBoolean(dataGridView1[9, i].Value));
                                    cmd.ExecuteNonQuery();
                                }
                                catch (Exception)
                                {
                                    continue;
                                }
                            }
                            else
                            {
                                MessageBox.Show(string.Format("{0} 身分證是否有空值,重複或長度異常", dataGridView1[0, i].Value));
                                MyClassLibrary.Class1.m = true;
                            
                            }
                        }
                    }
                }
            }
            this.toolStripTextBox2.Focus();
            this.toolStripTextBox2.SelectAll();
            int k = (from g in db.Patients
                     select g).Count();

            if (dataGridView1.Rows.Count > 1)
            {
                toolStripStatusLabel1.Text = "總筆數 :" + k;
                toolStripStatusLabel2.Text ="失敗:" + h;
            }

        }

        private void btn_Download_Click(object sender, EventArgs e)
        {
            if (dataGridView1.GetCellCount( DataGridViewElementStates.Displayed)==0)
            {
                MessageBox.Show("請將要匯出成 Excel 的內容 篩選至畫面");
            }
            else
            {
                MyClassLibrary.Class1.ExportDataGridview(dataGridView1, true);
            }
        }

        private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            toolStripStatusLabel2.Text = dataGridView1.SelectedRows[0].Index + 1 + " / " + dataGridView1.Rows.Count;
        }

        private void FrmPatient_KeyDown(object sender, KeyEventArgs e)
        {
          if (e.KeyCode == Keys.Enter)
            {
                btn_Search_Click(sender, e);
            }
        }



    }
    }

